class Vocabulary {
  constructor(db) {
    this.db = db;
  }

  /**
   * Initialize the vocabulary table
   */
  async initTable() {
    // Create vocabulary table
    const vocabSql = `
      CREATE TABLE IF NOT EXISTS vocabulary (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        word TEXT UNIQUE NOT NULL,
        phonetic TEXT,
        pronounce TEXT,
        trans TEXT,
        ielst TEXT DEFAULT 'NO',      -- YES/NO
        ok TEXT DEFAULT 'NO',         -- OK/NO
        uk_speech TEXT,               -- 英式发音文件路径
        us_speech TEXT,               -- 美式发音文件路径
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `;

    // Create definitions table
    const defSql = `
      CREATE TABLE IF NOT EXISTS definitions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vocab_id INTEGER,
        part_of_speech TEXT,
        definition TEXT,
        FOREIGN KEY (vocab_id) REFERENCES vocabulary(id) ON DELETE CASCADE
      )
    `;

    // Create examples table
    const exampleSql = `
      CREATE TABLE IF NOT EXISTS examples (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        vocab_id INTEGER,
        example TEXT,
        translation TEXT,
        FOREIGN KEY (vocab_id) REFERENCES vocabulary(id) ON DELETE CASCADE
      )
    `;

    // Create query history table
    const historySql = `
      CREATE TABLE IF NOT EXISTS query_history (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        word TEXT,
        queried_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `;

    try {
      await this.db.run(vocabSql);
      await this.db.run(defSql);
      await this.db.run(exampleSql);
      await this.db.run(historySql);
      console.log('Vocabulary tables initialized');
    } catch (err) {
      console.error('Error initializing vocabulary tables:', err);
    }
  }

  /**
   * Add a new word to the vocabulary
   * @param {Object} wordData - Word data to insert
   * @returns {Promise} - Promise that resolves with the inserted word ID
   */
  async addWord(wordData) {
    const sql = `
      INSERT INTO vocabulary (word, phonetic, pronounce, trans, ielst, ok, uk_speech, us_speech)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `;

    const params = [
      wordData.word,
      wordData.phonetic || '',
      wordData.pronounce || '',
      wordData.trans || '',
      wordData.ielst || 'NO',
      wordData.ok || 'NO',
      wordData.uk_speech || '',
      wordData.us_speech || '',
    ];

    try {
      const result = await this.db.run(sql, params);
      return result.id;
    } catch (err) {
      console.error('Error adding word:', err);
      throw err;
    }
  }

  /**
   * Get a word by its ID
   * @param {number} id - Word ID
   * @returns {Promise} - Promise that resolves with the word data
   */
  async getWordById(id) {
    const sql = 'SELECT * FROM vocabulary WHERE id = ?';
    try {
      return await this.db.get(sql, [id]);
    } catch (err) {
      console.error('Error getting word by ID:', err);
      throw err;
    }
  }

  /**
   * Get a word by its text
   * @param {string} word - Word text
   * @returns {Promise} - Promise that resolves with the word data
   */
  async getWordByText(word) {
    // Try exact match first
    let sql = 'SELECT * FROM vocabulary WHERE word = ?';
    try {
      console.log('Searching for word (exact match):', word);
      let wordData = await this.db.get(sql, [word]);
      console.log('Exact match result:', wordData);

      // If not found, try case-insensitive search
      if (!wordData) {
        sql = 'SELECT * FROM vocabulary WHERE LOWER(word) = LOWER(?)';
        console.log('Searching for word (case-insensitive):', word);
        wordData = await this.db.get(sql, [word]);
        console.log('Case-insensitive match result:', wordData);
      }

      // If word exists, get its definitions and examples
      if (wordData) {
        console.log('Found word, getting definitions and examples for ID:', wordData.id);
        const definitions = await this.getDefinitions(wordData.id);
        const examples = await this.getExamples(wordData.id);

        console.log('Definitions for word:', definitions);
        console.log('Examples for word:', examples);

        const result = {
          ...wordData,
          definitions: definitions || [],
          examples: examples || [],
        };

        console.log('Final result:', result);
        return result;
      }

      console.log('Word not found in database');
      return wordData || null;
    } catch (err) {
      console.error('Error getting word by text:', err);
      throw err;
    }
  }

  /**
   * Get definitions for a word
   * @param {number} vocabId - Vocabulary ID
   * @returns {Promise} - Promise that resolves with the definitions
   */
  async getDefinitions(vocabId) {
    const sql = 'SELECT * FROM definitions WHERE vocab_id = ?';
    try {
      return await this.db.all(sql, [vocabId]);
    } catch (err) {
      console.error('Error getting definitions:', err);
      throw err;
    }
  }

  /**
   * Get examples for a word
   * @param {number} vocabId - Vocabulary ID
   * @returns {Promise} - Promise that resolves with the examples
   */
  async getExamples(vocabId) {
    const sql = 'SELECT * FROM examples WHERE vocab_id = ?';
    try {
      return await this.db.all(sql, [vocabId]);
    } catch (err) {
      console.error('Error getting examples:', err);
      throw err;
    }
  }

  /**
   * Search for words that match a pattern
   * @param {string} pattern - Search pattern
   * @returns {Promise} - Promise that resolves with the matching words
   */
  async searchWords(pattern) {
    // Use case-insensitive search
    const sql = 'SELECT * FROM vocabulary WHERE LOWER(word) LIKE LOWER(?) ORDER BY word';
    try {
      console.log('Searching for pattern:', pattern);
      const results = await this.db.all(sql, [`%${pattern}%`]);
      console.log('Pattern search results for "' + pattern + '":', results);
      return results || [];
    } catch (err) {
      console.error('Error searching words:', err);
      throw err;
    }
  }

  /**
   * Search for words with prefix matching
   * @param {string} prefix - Prefix to search for
   * @param {number} limit - Maximum number of results to return
   * @returns {Promise} - Promise that resolves with the matching words
   */
  async searchWordsByPrefix(prefix, limit = 10) {
    const sql = 'SELECT * FROM vocabulary WHERE word LIKE ? ORDER BY word LIMIT ?';
    try {
      return await this.db.all(sql, [`${prefix}%`, limit]);
    } catch (err) {
      console.error('Error searching words by prefix:', err);
      throw err;
    }
  }

  /**
   * Search for words with suffix matching
   * @param {string} suffix - Suffix to search for
   * @param {number} limit - Maximum number of results to return
   * @returns {Promise} - Promise that resolves with the matching words
   */
  async searchWordsBySuffix(suffix, limit = 10) {
    const sql = 'SELECT * FROM vocabulary WHERE word LIKE ? ORDER BY word LIMIT ?';
    try {
      return await this.db.all(sql, [`%${suffix}`, limit]);
    } catch (err) {
      console.error('Error searching words by suffix:', err);
      throw err;
    }
  }

  /**
   * Calculate Levenshtein distance between two strings
   * @param {string} a - First string
   * @param {string} b - Second string
   * @returns {number} - Levenshtein distance
   */
  levenshteinDistance(a, b) {
    if (a.length === 0) return b.length;
    if (b.length === 0) return a.length;

    const matrix = Array(b.length + 1)
      .fill()
      .map(() => Array(a.length + 1).fill(null));

    for (let i = 0; i <= a.length; i++) {
      matrix[0][i] = i;
    }

    for (let j = 0; j <= b.length; j++) {
      matrix[j][0] = j;
    }

    for (let j = 1; j <= b.length; j++) {
      for (let i = 1; i <= a.length; i++) {
        const cost = a[i - 1] === b[j - 1] ? 0 : 1;
        matrix[j][i] = Math.min(
          matrix[j][i - 1] + 1, // insertion
          matrix[j - 1][i] + 1, // deletion
          matrix[j - 1][i - 1] + cost // substitution
        );
      }
    }

    return matrix[b.length][a.length];
  }

  /**
   * Search for similar words using fuzzy matching
   * @param {string} word - Word to find similar words for
   * @param {number} limit - Maximum number of results to return
   * @returns {Promise} - Promise that resolves with the similar words
   */
  async searchSimilarWords(word, limit = 10) {
    // First get a larger set of candidate words
    const candidateSql = `
      SELECT * 
      FROM vocabulary 
      WHERE LENGTH(word) BETWEEN ? AND ?
      ORDER BY word
    `;

    try {
      // Get candidate words with length within 3 characters of the search word
      const candidates = await this.db.all(candidateSql, [Math.max(1, word.length - 3), word.length + 3]);

      // Calculate similarity for each candidate
      const similarities = candidates.map(candidate => {
        // Calculate normalized similarity (0-1, where 1 is identical)
        const distance = this.levenshteinDistance(word.toLowerCase(), candidate.word.toLowerCase());
        const maxLength = Math.max(word.length, candidate.word.length);
        const similarity = 1 - distance / maxLength;
        return { ...candidate, similarity, distance };
      });

      // Sort by similarity (highest first) and limit results
      similarities.sort((a, b) => b.similarity - a.similarity);

      // Only return words with some similarity (at least 50%)
      const filtered = similarities.filter(item => item.similarity > 0.5);

      // Return top results
      return filtered.slice(0, limit);
    } catch (err) {
      console.error('Error searching similar words:', err);
      throw err;
    }
  }

  /**
   * Get words with pagination
   * @param {number} limit - Number of words to return
   * @param {number} offset - Number of words to skip
   * @returns {Promise} - Promise that resolves with the words
   */
  async getWords(limit = 10, offset = 0) {
    const sql = 'SELECT * FROM vocabulary ORDER BY word LIMIT ? OFFSET ?';
    const countSql = 'SELECT COUNT(*) as total FROM vocabulary';
    try {
      const words = await this.db.all(sql, [limit, offset]);
      const countResult = await this.db.get(countSql);
      return {
        words,
        total: countResult.total,
      };
    } catch (err) {
      console.error('Error getting words:', err);
      throw err;
    }
  }

  /**
   * Add a query history record
   * @param {string} word - The queried word
   * @returns {Promise} - Promise that resolves with the inserted record ID
   */
  async addQueryHistory(word) {
    // First check if the word already exists in history
    const checkSql = 'SELECT id FROM query_history WHERE word = ? ORDER BY queried_at DESC LIMIT 1';
    try {
      const existingRecord = await this.db.get(checkSql, [word]);

      if (existingRecord) {
        // If word exists in history, update the queried_at timestamp
        const updateSql = 'UPDATE query_history SET queried_at = CURRENT_TIMESTAMP WHERE id = ?';
        await this.db.run(updateSql, [existingRecord.id]);
        return existingRecord.id;
      } else {
        // If word doesn't exist in history, insert a new record
        const insertSql = 'INSERT INTO query_history (word) VALUES (?)';
        const result = await this.db.run(insertSql, [word]);
        return result.id;
      }
    } catch (err) {
      console.error('Error adding query history:', err);
      throw err;
    }
  }

  /**
   * Get query history
   * @param {number} limit - Number of records to return
   * @returns {Promise} - Promise that resolves with the query history
   */
  async getQueryHistory(limit = 50) {
    // Use a query that groups by word and gets the latest record for each word
    const sql = `
      SELECT * FROM query_history q1
      WHERE q1.queried_at = (
        SELECT MAX(q2.queried_at)
        FROM query_history q2
        WHERE q2.word = q1.word
      )
      ORDER BY q1.queried_at DESC 
      LIMIT ?
    `;
    try {
      return await this.db.all(sql, [limit]);
    } catch (err) {
      console.error('Error getting query history:', err);
      throw err;
    }
  }

  /**
   * Clear query history
   * @returns {Promise} - Promise that resolves with the operation result
   */
  async clearQueryHistory() {
    const sql = 'DELETE FROM query_history';
    try {
      return await this.db.run(sql);
    } catch (err) {
      console.error('Error clearing query history:', err);
      throw err;
    }
  }

  /**
   * Remove a single item from query history
   * @param {string} word - The word to remove from history
   * @returns {Promise} - Promise that resolves with the operation result
   */
  async removeQueryHistoryItem(word) {
    const sql = 'DELETE FROM query_history WHERE word = ?';
    try {
      return await this.db.run(sql, [word]);
    } catch (err) {
      console.error('Error removing query history item:', err);
      throw err;
    }
  }

  /**
   * Update word's known status
   * @param {string} word - Word text
   * @param {string} status - Status ('YES' or 'NO')
   * @returns {Promise} - Promise that resolves with the update result
   */
  async updateKnownStatus(word, status) {
    // Validate inputs
    if (!word || typeof word !== 'string') {
      throw new Error('Invalid word parameter');
    }

    if (status !== 'YES' && status !== 'NO') {
      throw new Error('Invalid status parameter. Must be "YES" or "NO"');
    }

    const sql = 'UPDATE vocabulary SET ok = ?, updated_at = CURRENT_TIMESTAMP WHERE word = ?';
    try {
      // First check if the word exists
      const existingWord = await this.getWordByText(word);
      if (!existingWord) {
        throw new Error(`Word "${word}" not found`);
      }

      const result = await this.db.run(sql, [status, word]);
      return result;
    } catch (err) {
      console.error('Error updating known status:', err);
      throw err;
    }
  }

  /**
   * Update word's IELTS tag
   * @param {string} word - Word text
   * @param {string} tag - Tag ('YES' or 'NO')
   * @returns {Promise} - Promise that resolves with the update result
   */
  async updateIeltsTag(word, tag) {
    // Validate inputs
    if (!word || typeof word !== 'string') {
      throw new Error('Invalid word parameter');
    }

    if (tag !== 'YES' && tag !== 'NO') {
      throw new Error('Invalid tag parameter. Must be "YES" or "NO"');
    }

    const sql = 'UPDATE vocabulary SET ielst = ?, updated_at = CURRENT_TIMESTAMP WHERE word = ?';
    try {
      // First check if the word exists
      const existingWord = await this.getWordByText(word);
      if (!existingWord) {
        throw new Error(`Word "${word}" not found`);
      }

      const result = await this.db.run(sql, [tag, word]);
      return result;
    } catch (err) {
      console.error('Error updating IELTS tag:', err);
      throw err;
    }
  }
}

module.exports = Vocabulary;
